package com.ydgk.dao;

import com.ydgk.bean.User;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.DataIntegrityViolationException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.lang.Nullable;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDao extends BaseDao<User>{

    @Override
    public int save(User user) {
        return this.template.update("insert into y_user values(null,?,?,?)",user.getPhone(),user.getName(),user.getAddress());
    }

    @Override
    public int updateById(User user) {
        return this.template.update("update y_user set phone=?,name=?,address=? where id=?",user.getPhone(),user.getName(),user.getAddress(),user.getId());
    }

    @Override
    public int deleteById(int id) {
        try {
            return template.update("delete from y_user where id=?", id);
        }catch (DataIntegrityViolationException e){
            return -1;
        }
    }

    @Override
    public List findAll() {
        return this.template.query("select * from y_user",new BeanPropertyRowMapper<User>(User.class));
    }

    @Override
    public User findById(int id) {
        return null;
    }

    @Override
    public List paging(int pageNumber) {
        return null;
    }

    public List<User> adminSearch(String phone,Integer pageNumber){
        StringBuilder sql=new StringBuilder("select * from y_user u ");
        List params = new ArrayList();
        if(phone != null && !phone.equals("")){
            sql.append(" where u.phone like ?");
            params.add("%"+phone+"%");
        }
        sql.append(" limit ?,?");
        params.add((pageNumber-1)*pageCount);
        params.add(pageCount);
        return this.template.query(sql.toString(), new ResultSetExtractor<List<User>>() {
            @Nullable
            @Override
            public List<User> extractData(ResultSet rs) throws SQLException, DataAccessException {
                List<User> userList = new ArrayList<User>();
                while (rs.next()) {
                    User user = new User();
                    user.setId(rs.getInt("id"));
                    user.setPhone(rs.getString("phone"));
                    user.setName(rs.getString("name"));
                    user.setAddress(rs.getString("address"));
                    userList.add(user);
                }
                return userList;
            }
        },params.toArray());
    }
    public int adminSearchMaxPage(String phone) {
        StringBuilder sql = new StringBuilder("select count(*) from y_user u ");
        List params = new ArrayList();
        if (phone != null && !phone.equals("")) {
            sql.append(" where u.phone like ?");
            params.add("%" + phone + "%");
        }
        long t = this.template.queryForObject(sql.toString(),Long.class,params.toArray());

        int total = (int)t;//将long类型转换成int类型
        return total % pageCount == 0 ? total / pageCount : total / pageCount + 1 ;
    }
}
